package zhuzx.solution;

import java.awt.Toolkit;
import java.awt.datatransfer.Clipboard;
import java.awt.datatransfer.StringSelection;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.swing.JOptionPane;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 * 直接运行程序，输入表名，可生成该表的bex代码、doc表格并自动复制到剪贴板。<br>
 * 数据库驱动必须实现JDBC-4.1的接口才能运行，比如mysql-connector-java-5.0.8是不可用的。<br>
 * 例如该版本没有实现Connection.getSchema()方法。<br>
 * 又如该版本的DatabaseMetaData.getColumns方法不够完善，不能获取date、datetime等类型的长度。<br>
 * mysql-connector-java-5.1.28无问题。
 * @author	zhuzx	2016年2月4日
 */
public class BexCreator {
	private static final String fileName = "E:/SVN/iWeLan-cms/4.Trunk/4.2CMS-BOSS/kwdf_manage_bhboss/"
			+ "WebRoot/WEB-INF/kwdf/system/frame-jdbc.xml";
	private static final String dataSource = "iwelan";
	
	public static void main(String[] args) throws Exception {
		String inputValue = JOptionPane.showInputDialog("请输入表名：");
		String[] options = new String[]{"bex", "doc"};
		int selected = JOptionPane.showOptionDialog(null, "请选择生成bex代码或者doc表格：", "生成器",
				JOptionPane.DEFAULT_OPTION, JOptionPane.QUESTION_MESSAGE, null,
				options, options[0]);
		String str = null;
		if (selected == 0) {
			str = createBaseBex(getConnection(), inputValue);
		} else if (selected == 1) {
			str = createDocTable(getConnection(), inputValue);
		}
		Toolkit toolkit = Toolkit.getDefaultToolkit();
		Clipboard clipboard = toolkit.getSystemClipboard();
		StringSelection ss = new StringSelection(str);
		clipboard.setContents(ss, null);
	}
	
	public static String createBaseBex(Connection conn, String tableName) throws SQLException {
		String[][] columns = getColumns(conn, tableName);
		StringBuilder sb = new StringBuilder();
		sb.append("\n\t<bex id=\"\" name=\"\" type=\"jdbc\" dataSource=\"" + dataSource
				+ "\" table=\"" + tableName + "\">\n");
		for (String[] column : columns) {
			sb.append("\t\t<param name=\"" + column[0] + "\" type=\"" + column[1] + "\"/>\n");
		}
		sb.append("\t</bex>\n");
		return sb.toString();
	}
	
	public static String createDocTable(Connection conn, String tableName) throws SQLException {
		String[][] columns = getColumns(conn, tableName);
		StringBuilder sb = new StringBuilder();
		for (String[] column : columns) {
			sb.append(column[0] + "\t" + column[1] + "(" + column[2] + ")" + "\t" + column[3] + "\n");
		}
		return sb.toString();
	}
	
	public static Connection getConnection() throws Exception {
		Map<String,String> conf = readConfParams();
		String url = conf.get("jdbcUrl");
		System.setProperty("jdbc.drivers", conf.get("driverClass"));
		return DriverManager.getConnection(url, conf.get("user"), conf.get("password"));
	}
	
	public static Map<String,String> readConfParams() throws Exception {
		DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
		DocumentBuilder builder = dbf.newDocumentBuilder();
		FileInputStream is = new FileInputStream(fileName);
		Document doc = builder.parse(is);
		Element elem = getChild(doc.getDocumentElement(), "bean", "id", "dataConfig");
		elem = getChild(elem, "property", "name", dataSource);
		elem = (Element) elem.getElementsByTagName("map").item(0);
		Map<String,String> conf = new HashMap<String,String>();
		conf.put("dataSource", dataSource);
		NodeList entrys = elem.getElementsByTagName("entry");
		for (int i=0; i<entrys.getLength(); i++) {
			Element entry = (Element) entrys.item(i);
			conf.put(entry.getAttribute("name"), entry.getAttribute("value"));
		}
		return conf;
	}
	
	public static Element getChild(Element parent, String tagName, String key, String value) {
		NodeList children = parent.getElementsByTagName(tagName);
		for (int i=0; i<children.getLength(); i++) {
			Element elem = (Element) children.item(i);
			if (elem.getAttribute(key).equals(value)) {
				return elem;
			}
		}
		return null;
	}

	@Deprecated
	public static Element getChild(Element parent, String key, String value) {
		NodeList children = parent.getChildNodes();
		for (int i=0; i<children.getLength(); i++) {
			Node node = children.item(i);
			if (node instanceof Element) {
				Element elem = (Element) node;
				if (elem.getAttribute(key).equals(value)) {
					return elem;
				}
			}
		}
		return null;
	}
	
	/**
	 * 获取指定表的字段信息。
	 * @return 由字段信息组成的二维数组，每个子数组对应一个字段，其中有四个元素：字段名，类型，长度，注释。
	 */
	public static String[][] getColumns(Connection conn, String tableName) throws SQLException {
		//替代方案：执行一次"select *"查询，然后使用ResultSetMetaData.getColumnCount()获取字段数，但多一次数据库操作。
		DatabaseMetaData dmd = conn.getMetaData();
		ResultSet rs = dmd.getColumns(conn.getCatalog(), conn.getSchema(), tableName ,null);
		List<String> names = new ArrayList<String>();
		List<String> types = new ArrayList<String>();
		List<String> sizes = new ArrayList<String>();
		List<String> comments = new ArrayList<String>();
		while (rs.next()) {
			names.add(rs.getString("COLUMN_NAME"));
			types.add(rs.getString("TYPE_NAME").toLowerCase());
			sizes.add(rs.getString("COLUMN_SIZE"));
			comments.add(rs.getString("REMARKS"));
		}
		rs.close();
		String[][] columns = new String[names.size()][4];
		for (int i=0; i<columns.length; i++) {
			columns[i][0] = names.get(i);
			columns[i][1] = types.get(i);
			columns[i][2] = sizes.get(i);
			columns[i][3] = comments.get(i);
		}
		return columns;
	}

	@Deprecated
	public static String[][] getColumnsTest(Connection conn, String tableName) throws SQLException {
		
		String sql = "select * from " + tableName;
		ResultSetMetaData rsmd = conn.createStatement().executeQuery(sql).getMetaData();
		for (int i=0; i<rsmd.getColumnCount(); i++) {
			System.out.println(rsmd.getColumnLabel(i+1) + " " + rsmd.getColumnTypeName(i+1));
		}
		
		//--------------------分割线---------------------
		
		//替代方案：执行一次"select *"查询，然后使用ResultSetMetaData.getColumnCount()获取字段数，但多一次数据库操作。
		DatabaseMetaData dmd = conn.getMetaData();
		ResultSet rs = dmd.getColumns(conn.getCatalog(), conn.getSchema(), tableName ,null);
		List<String> names = new ArrayList<String>();
		List<String> types = new ArrayList<String>();
		List<String> sizes = new ArrayList<String>();
		List<String> comments = new ArrayList<String>();
		while (rs.next()) {
			names.add(rs.getString("COLUMN_NAME"));
			types.add(rs.getString("TYPE_NAME").toLowerCase());
			sizes.add(rs.getString("COLUMN_SIZE"));
			comments.add(rs.getString("REMARKS"));
		}
		rs.close();
		String[][] columns = new String[names.size()][4];
		for (int i=0; i<columns.length; i++) {
			columns[i][0] = names.get(i);
			columns[i][1] = types.get(i);
			columns[i][2] = sizes.get(i);
			columns[i][3] = comments.get(i);
		}
		return columns;
	}

}
